/*
 * Copyright 2010, Andrew M Gibson
 *
 * www.andygibson.net
 *
 * This file is part of DataValve.
 *
 * DataValve is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * DataValve is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with DataValve.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.fluttercode.datavalve.provider.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.fluttercode.datavalve.params.Parameter;
import org.fluttercode.datavalve.provider.AbstractQueryDataProvider;
import org.fluttercode.datavalve.provider.QueryDataProvider;
import org.fluttercode.datavalve.provider.util.DataQuery;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Provides the base implementation for a SQL based {@link QueryDataProvider}
 * which uses restrictions, parameters and ordering to define the final results.
 *
 * Because this is a SQL based dataset, we need some mechanism to turn the raw
 * data into an object. We do this using the template pattern by defining the
 * method
 * {@link AbstractJdbcDataProvider#createListFromResultSet(ResultSet, Integer, Integer)
 * as abstract. This will be implemented in sub classes to convert the result
 * set data into a strongly typed object.
 *
 * @author Andy Gibson
 *
 * @param <T> The type of object that will be returned in the dataset.
 */
public abstract class AbstractJdbcDataProvider<T> extends AbstractQueryDataProvider<T> {

    private static final long serialVersionUID = 1L;
    private static Logger log = LoggerFactory.getLogger(AbstractJdbcDataProvider.class);

    private transient Connection connection;

    public AbstractJdbcDataProvider() {
        this(null);
    }

    public AbstractJdbcDataProvider(Connection connection) {
        this.connection = connection;
        // PreparedStatement中SQL语句的参数占位符“:param”需要替换成JDBC标准的“?”
        this.setOrderedParams(true);
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * Creates a new {@link PreparedStatement} for use in fetching the count or
     * the results.
     *
     * @param query {@link DataQuery} instance that contains the query sql and parameters
     * @return the JDBC prepared statement reflecting the {@link DataQuery}
     * @throws SQLException
     */
    private PreparedStatement buildPreparedStatement(DataQuery query) throws SQLException {
        String sql = query.getStatement();
        log.debug("Debug: buildPreparedStatement(), sql = {}", sql);
        //this.addParameter(sql, sql);
        List<Parameter> params = query.getParameters();
        log.debug("Debug: buildPreparedStatement(), params.size() = {}", params.size());
        DatabaseMetaData metaData = connection.getMetaData();
        int resultSetType = (metaData.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE) ? ResultSet.TYPE_SCROLL_INSENSITIVE : ResultSet.TYPE_FORWARD_ONLY);
        int resultSetConcurrency = ResultSet.CONCUR_READ_ONLY;
        PreparedStatement statement = connection.prepareStatement(sql, resultSetType, resultSetConcurrency);
        for (int i = 0; i < params.size(); i++) {
            Parameter param = params.get(i);
            log.debug("Setting parameter {} to '{}'", i + 1, param.getValue());
            statement.setObject(i + 1, param.getValue());
        }

        return statement;
    }

    @Override
    protected Integer queryForCount(DataQuery query) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int count = 0;
        try {
            statement = buildPreparedStatement(query);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
                log.debug("Fetch Result Count SQL returned {}", count);
            } else {
                log.debug("ResultSet is empty");
            }
        } catch (SQLException ex) {
            //ex.printStackTrace();
            log.error("查询记录数时SQL异常：" + ex.getMessage(), ex);
        } finally {
            DbUtils.closeQuietly(resultSet);
            DbUtils.closeQuietly(statement);
        }
        return count;
    }

    @Override
    protected List<T> queryForResults(DataQuery query, Integer firstResult, Integer count) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = buildPreparedStatement(query);
            resultSet = statement.executeQuery();
            List<T> results = createListFromResultSet(resultSet, firstResult, count);
            log.debug("Results processor returned {} results", results.size());
            return results;
        } catch (SQLException ex) {
            log.error("查询时SQL异常：" + ex.getMessage(), ex);
        } finally {
            DbUtils.closeQuietly(resultSet);
            DbUtils.closeQuietly(statement);
        }
        return Collections.emptyList();
    }

    /**
     * Iterate over the given result set calling the
     * {@link AbstractJdbcQueryDataProvider#resultSetObjectMapper} for each row
     * in the result set. Each created object is added to the result list which
     * is then returned to the user.
     *
     * @param resultSet list of records we are converting to objects.
     * @param firstRow number of the row we start processing from
     * @param maxRows used to limit the number of rows we process (null indicates all rows)
     * @return List of objects derived from the result set from firstRow to firstRow + maxRows or to the end of the resultset if maxRows == 0
     * @throws SQLException
     */
    protected List<T> createListFromResultSet(ResultSet resultSet,
            Integer firstRow, Integer maxRows) throws SQLException {
        List<T> results = new ArrayList<T>();
        if (!skipRows(resultSet, firstRow)) {
            return results;
        }

        while (true) {
            results.add(createObjectFromResultSet(resultSet));
            // if this is paged, limit the number of rows loaded
            if (maxRows != null && results.size() == maxRows) {
                return results;
            }
            // if there are no more results, return the list.
            if (!resultSet.next()) {
                return results;
            }
        }
    }

    /**
     * Moves the result set to the row indicated by first row.
     *
     * @todo 由于未在查询语句中使用翻页特性，此方法性能较低，可参照Hibernate进行改进(ljg 20150119)
     *
     * @param resultSet Result set to skip rows in
     * @param firstRow number of rows we need to skip, null or zero for the first row
     * @return returns true if there are more rows left to process, or false if the end of the result set was reached
     * @throws SQLException
     */
    private boolean skipRows(ResultSet resultSet, Integer firstRow) throws SQLException {
        log.debug("Debug: skipRows(), firstRow={}", firstRow);
        // if firstRow is null, then just return
        if (firstRow == null) {
            firstRow = 0;
        }
//        int index = 0;
//        while (index < firstRow && resultSet.next()) {
//            index++;
//        }
        try {
            if (firstRow > 0) {
                resultSet.absolute(firstRow);
                log.debug("SQL Feature resultSet.absolute({}) supported :-)", firstRow);
            }
        } catch (SQLFeatureNotSupportedException sfnse) {
            log.warn("SQL Feature resultSet.absolute({}) not supported: {}", firstRow, sfnse.getMessage());
            int index = 0;
            while (index < firstRow && resultSet.next()) {
                index++;
            }
        } catch (SQLException sqle) {
            // com.microsoft.sqlserver.jdbc.SQLServerException: 只进结果集不支持请求的操作。
            log.warn("SQL Feature resultSet.absolute({}) not supported: {}", firstRow, sqle.getMessage());
            int index = 0;
            while (index < firstRow && resultSet.next()) {
                index++;
            }
        }

        return resultSet.next();
    }

    public abstract T createObjectFromResultSet(ResultSet resultSet) throws SQLException;
}
